package com.felix.fund;


import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;


public class DBConnUtils {

    //mysql驱动包名
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    //数据库连接地址
    private static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&serverTimezone=GMT%2B8";
    //用户名
    private static final String USER_NAME = "root";
    //密码
    private static final String PASSWORD = "123456";

    public static List<String> getColumn(String sql, String columnName){
        Connection connection = null;
        List<String> list = new ArrayList<String>();
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            //mysql查询语句
            PreparedStatement prst = connection.prepareStatement(sql);
            //结果集
            ResultSet rs = prst.executeQuery();

            while (rs.next()) {
                list.add(rs.getString(columnName));
            }
            rs.close();
            prst.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    public static List<String> getData(String sql, String columnName, List<String> l){
        Connection connection = null;
        List<String> list = new ArrayList<String>();
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            //mysql查询语句
            PreparedStatement prst = connection.prepareStatement(sql);
            for (int i=0;i<l.size();i++) {
                prst.setString(i+1, l.get(i));
            }
            //结果集
            ResultSet rs = prst.executeQuery();

            while (rs.next()) {
                list.add(rs.getString(columnName));
            }
            rs.close();
            prst.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    public static String updateData(String sql, List<String> l){
        Connection connection = null;
        String updateFlag = "";
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            //mysql查询语句
            PreparedStatement prst = connection.prepareStatement(sql);
            for (int i=0;i<l.size();i++) {
                prst.setString(i+1, l.get(i));
            }
            //结果集
            int i = prst.executeUpdate();

            if (i>0){
                updateFlag = "succ";
            }else{
                updateFlag = "fail";
            }
            prst.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return updateFlag;
    }

}
